﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelTotalObject
    {
        public const string sql = @"
SELECT 'Закуплено топливо на склад:' as Итоги, '' as Топливо, '' as Литры 
UNION ALL
SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		convert(nchar(20),sum(t.Liters)) as Литры
FROM Transactions as t
LEFT JOIN DictionaryCategories on t.Category = DictionaryCategories.ID
WHERE t.Category in (select id from DictionaryCategories where Fuel = 1)
GROUP BY DictionaryCategories.Name

UNION ALL
SELECT 'Всего заправлено в машины:' as Итоги, '' as Топливо, '' as Литры 
UNION ALL

SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		convert(nchar(20),sum(r.Liters)) as Литры
FROM Refueling as r
LEFT JOIN DictionaryCategories on r.Fuel = DictionaryCategories.ID
WHERE r.FuelOperType = 1
GROUP BY DictionaryCategories.Name

UNION ALL
SELECT 'Остаток топлива:' as Итоги, '' as Топливо, '' as Литры 
UNION ALL
SELECT '' as Итоги, TOTAL.Топливо as Топливо, convert(nchar(20),sum(TOTAL.Liters)) as Литры
FROM
(SELECT '' as Итоги,
		DictionaryCategories.Name as Топливо,
		sum(t.Liters) as Liters
FROM Transactions as t
LEFT JOIN DictionaryCategories on t.Category = DictionaryCategories.ID
WHERE t.Category in (select id from DictionaryCategories where Fuel = 1)
GROUP BY DictionaryCategories.Name
union all
SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		-sum(r.Liters) as Liters
FROM Refueling as r
LEFT JOIN DictionaryCategories on r.Fuel = DictionaryCategories.ID
WHERE r.FuelOperType = 1
GROUP BY DictionaryCategories.Name) as TOTAL
GROUP BY TOTAL.Топливо
";
        public string Итоги { get; set; }
        public string Топливо { get; set; }
        public string Литры { get; set; }
    }
}
